<?xml version="1.0" encoding="iso-8859-1"?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>FusionCharts Free Documentation</title>
<link rel="stylesheet" href="Style.css" type="text/css" />
</head>

<body>
<table width="98%" border="0" cellspacing="0" cellpadding="3" align="center">
  <tr> 
    <td><h2 class="pageHeader">Using FusionCharts PHP Class &gt; Creating Drill-down charts </h2></td>
  </tr>
  <tr> 
    <td valign="top" class="text"><p>In our previous example, we had used FusionCharts to plot a chart using data stored in database. We'll now extend that example itself to create a drill-down chart which can show more information.</p>
    <p><strong>Before you go further with this page, we recommend you to please see the previous sections like &quot;Basic Examples&quot;, Creating Data from Array&quot; as we start off from concepts explained in those pages. </strong></p>
    
	<p>If you recall from previous example, we were showing the sum of factory output in a Pie chart as under: </p></td>
  </tr>
  <tr>
    <td valign="top" class="text"><img src="Images/Code_DBOut.jpg" class="imageBorder" /></td>
  </tr>
  <tr>
    <td valign="top" class="text">In this example, we'll extend this example, so that when a user clicks on a Pie slice for a factory, he can drill down to see date wise production for that factory. </td>
  </tr>
  <tr>
    <td valign="top" class="text">&nbsp;</td>
  </tr>
  <tr>
    <td valign="top" class="header">Setting up the pie chart for Link </td>
  </tr>
  <tr>
    <td valign="top" class="text">To set up the pie chart to enable links for drill-down involves just minor tweaking of our previous <span class="codeInline">BasicDBExample.php</span>. We basically need to add the <span class="codeInline">link</span> attribute for each<span class="codeInline"> &lt;set&gt;</span> element. We create a new page <span class="codeInline">Default.php</span> (in DB_DrillDown folder) from the previous page with the   following code changes:
    <p class="highlightBlock">The code examples contained in this page are contained in<span class="codeInline"> Download Package &gt; Code &gt; PHPClass &gt; DB_DrillDown</span> folder. </p></td>
  </tr>
  <tr>
    <td valign="top" class="codeBlock">
      <p>&lt;?php<br />
        &nbsp;&nbsp;<span class="codeComment">//We've included ../Includes/FusionCharts_Gen.php, which contains<br />
        &nbsp;&nbsp;//FusionCharts PHP Class to help us easily embed charts <br />
        &nbsp;&nbsp;//We've also used ../Includes/DBConn.php to easily connect to a database</span><br />
        &nbsp;&nbsp;include(&quot;../Includes/FusionCharts_Gen.php&quot;);<br />
        &nbsp;&nbsp;include(&quot;../Includes/DBConn.php&quot;);<br />
        ?&gt;<br />
        &lt;HTML&gt;<br />
        &lt;HEAD&gt;<br />
&nbsp;&nbsp;&lt;TITLE&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;FusionCharts Free - Database and Drill-Down Example<br />
&lt;/TITLE&gt;<br />
<br />
&lt;?php<br />
<span class="codeComment">&nbsp;&nbsp;//You need to include the following JS file, if you intend to embed the chart using JavaScript.<br />
&nbsp;&nbsp;//Embedding using JavaScripts avoids the &quot;Click to Activate...&quot; issue in Internet Explorer<br />
&nbsp;&nbsp;//When you make your own charts, make sure that the path to this JS file is correct. Else, you &nbsp;&nbsp;//would get JavaScript errors.</span><br />
?&gt; <br />
&nbsp;&nbsp;&lt;SCRIPT LANGUAGE=&quot;Javascript&quot; SRC=&quot;../../FusionCharts/FusionCharts.js&quot;&gt;&lt;/SCRIPT&gt;<br />
<br />
&lt;/HEAD&gt;<br />
&lt;BODY&gt;<br />
&lt;CENTER&gt;<br />
<br />
&lt;?php<br />
        <span class="codeComment">&nbsp;&nbsp;//In this example, we show how to connect FusionCharts to a database.<br />
        &nbsp;&nbsp;//For the sake of ease, we've used an MySQL databases containing two<br />
      &nbsp;&nbsp;//tables.</span></p>
      <p> <span class="codeComment">&nbsp;&nbsp;# Connect to the Database</span><br />
      &nbsp;&nbsp;$link = connectToDB();</p>
      <p> <span class="codeComment">&nbsp;&nbsp;# Create a pie 3d chart object</span><br />
      &nbsp;&nbsp;$FC = new FusionCharts(&quot;Pie3D&quot;,&quot;650&quot;,&quot;450&quot;); </p>
      <p> <span class="codeComment">&nbsp;&nbsp;# Set Relative Path of chart swf file.</span><br />
        &nbsp;&nbsp;$FC-&gt;setSwfPath(&quot;../../FusionCharts/&quot;);<br />
  <br />
        <span class="codeComment">&nbsp;&nbsp;#Store chart attributes in a variable</span><br />
      &nbsp;&nbsp;$strParam=&quot;caption=Factory Output report;subCaption=By Quantity;pieSliceDepth=30;showBorder=1; showNames=1;formatNumberScale=0;numberSuffix= Units&quot;;</p>
      <p> <span class="codeComment">&nbsp;&nbsp;#  Set chart attributes</span><br />
        &nbsp;&nbsp;$FC-&gt;setChartParams($strParam);<br />
  <br />
        <span class="codeComment">&nbsp;&nbsp;# Fetch all factory records creating SQL query</span><br />
        &nbsp;&nbsp;$strQuery = &quot;select a.FactoryID, b.FactoryName, sum(a.Quantity) as total from Factory_output a, Factory_Master b where a.FactoryId=b.FactoryId group by a.FactoryId,b.FactoryName&quot;;<br />
        <br />
        &nbsp;&nbsp;$result = mysql_query($strQuery) or die(mysql_error());<br />
  <br />
        <span class="codeComment">&nbsp;&nbsp;#Pass the SQL query result and Drill-Down link format to PHP Class Function<br />
        &nbsp;&nbsp;# this function will automatically add chart data from database</span><br />
        <span class="codeComment"><strong>&nbsp; /*<br />
        &nbsp;&nbsp; The last parameter passed i.e. &quot;Detailed.php?FactoryId=##FactoryID##&quot;<br />
&nbsp;&nbsp; drill down link from the current chart&nbsp; &nbsp; <br />
&nbsp;&nbsp;
Here, the link redirects to another PHP file Detailed.php <br />
&nbsp;&nbsp; with a query string variable -FactoryId<br />   
&nbsp;&nbsp;
whose value would be taken from the Query result created above.<br />
&nbsp;&nbsp; Any thing placed between ## and ## will be regarded <br />
&nbsp;&nbsp; as a field/column name in the SQL query result.<br />
&nbsp;&nbsp; Value from that column will be dynamically assingned as the query variable's value<br />
&nbsp;&nbsp; Hence, for each dataplot in the chart the resultant query variable's value<br />
&nbsp;&nbsp; will be different<br />
&nbsp; */<br />
        </strong></span><br />
        &nbsp;&nbsp;if ($result) <br />
        &nbsp;&nbsp;{<br />
        &nbsp;&nbsp;&nbsp;&nbsp;$FC-&gt;addDataFromDatabase($result, &quot;total&quot;, &quot;FactoryName&quot;,&quot;&quot;,<strong>&quot;Detailed.php?FactoryId=##FactoryID##&quot;</strong>);<br />
        &nbsp;&nbsp;}      </p>
      <p> &nbsp;&nbsp;mysql_close($link);</p>
      <p> <span class="codeComment">&nbsp;&nbsp;#Create the chart </span><br />
        &nbsp;&nbsp;$FC-&gt;renderChart();<br />
        ?&gt;<br />
        <br />
  &lt;/CENTER&gt;<br />
  &lt;/BODY&gt;<br />
  &lt;/HTML&gt;</p></td>
  </tr>
  <tr>
     <td valign="top" class="text">&nbsp;</td>
  </tr>
  <tr>
    <td valign="top" class="text">As you can see in the code above, we're doing the following:</td>
  </tr>
  <tr>
    <td valign="top" class="text" style="line-height:20px;">
      <ol>
        <li>Include <span class="codeInline">FusionCharts.js</span> JavaScript class, <span class="codeInline">FusionCharts.php</span> and <span class="codeInline">DbConn.php</span> to enable easy embedding of FusionCharts. <span class="codeInline">DBConn.php</span> contains connection parameters to connect to MySQL database.</li>
        <li>Call <span class="codeInline">connectToDB()</span> function to set up database connection</li>
        <li>Create FusionCharts PHP class object for Pie 3D chart</li>
        <li>Set relative  path of chart SWF file</li>
        <li>Store chart attributes in a variable <span class="codeInline">$strParam</span></li>
        <li>Set chart attributes using <span class="codeInline">setChartParams()</span> function</li>
        <li>Fetch required records from database and store the output in <span class="codeInline">$result</span></li>
        <li>Call <span class="codeInline">addDataFromDatabase()</span> function to add chart data from database; we specify the link attribute which points to <span class="codeInline">Detailed.php</span> page that contains the chart to show details.The last parameter passed i.e. <span class="codeInline">&quot;Detailed.php?FactoryId=##FactoryID##&quot;</span> drill down link from the current chart. Here, the link redirects to another PHP file Detailed.php  with a query string variable -FactoryId whose value would be taken from the Query result created above. Any thing placed between ## and ## will be regarded  as a field/column name in the SQL query result. Value from that column will be dynamically assingned as the query variable's value. Hence, for each dataplot in the chart the resultant query variable's value will be different.</li>
        <li>Close database link</li>
        <li>Render chart       </li>
      </ol>    </td>
  </tr>
  <tr>
    <td valign="top" class="highlightBlock">Please go through <a href="PHPClassAPI/Functions.html">FusionCharts PHP Class API Reference</a> section to know more about the functions used in the above code. </td>
	</tr>
  <tr>
    <td valign="top" class="text">&nbsp;</td>
  </tr>
  <tr>
    <td valign="top" class="text">Let's now shift our attention to <span class="codeInline">Detailed.php</span> page. </td>
  </tr>
  <tr>
    <td valign="top" class="text">&nbsp;</td>
  </tr>
  <tr>
    <td valign="top" class="header">Creating the detailed data chart page </td>
  </tr>
  <tr>
    <td valign="top" class="text">The page <span class="codeInline">Detailed.php</span> contains the following code: </td>
  </tr>
  <tr>
    <td valign="top" class="codeBlock">
      <p>&lt;?php<br />
        &nbsp;&nbsp;<span class="codeComment">//We've included ../Includes/FusionCharts_Gen.php, which contains<br />
        &nbsp;&nbsp;//FusionCharts PHP Class to help us easily embed charts <br />
        &nbsp;&nbsp;//We've also used ../Includes/DBConn.php to easily connect to a database</span><br />
        &nbsp;&nbsp;include(&quot;../Includes/FusionCharts_Gen.php&quot;);<br />
        &nbsp;&nbsp;include(&quot;../Includes/DBConn.php&quot;);<br />
        ?&gt;<br />
        &lt;HTML&gt;<br />
        &lt;HEAD&gt;<br />
&nbsp;&nbsp;&lt;TITLE&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;FusionCharts Free - Database and Drill-Down Example<br />
&nbsp;&nbsp;&lt;/TITLE&gt;<br />
<br />
&lt;?php<br />
<span class="codeComment">&nbsp;&nbsp;//You need to include the following JS file, if you intend to embed the chart using JavaScript.<br />
&nbsp;&nbsp;//Embedding using JavaScripts avoids the &quot;Click to Activate...&quot; issue in Internet Explorer<br />
&nbsp;&nbsp;//When you make your own charts, make sure that the path to this JS file is correct. <br />
&nbsp;&nbsp;//Else, you would get JavaScript errors.</span><br />
?&gt; <br />
&nbsp;&nbsp;&lt;SCRIPT LANGUAGE=&quot;Javascript&quot; SRC=&quot;../../FusionCharts/FusionCharts.js&quot;&gt;&lt;/SCRIPT&gt;<br />
<br />
&lt;/HEAD&gt;<br />
&lt;BODY&gt;<br />
&lt;CENTER&gt;<br />
<br />
        &lt;?php<br />
        <span class="codeComment">&nbsp;&nbsp;//This page is invoked from Default.php. When the user clicks on a pie<br />
        &nbsp;&nbsp;//slice in Default.php, the factory Id is passed to this page. We need<br />
        &nbsp;&nbsp;//to get that factory id, get information from database and then show<br />
      &nbsp;&nbsp;//a detailed chart.</span></p>    
      <p><span class="codeComment"> &nbsp;&nbsp;<strong>//Request the factory Id from Querystring, we sent the FactoryId from Default.php  </strong></span><br />
        &nbsp;&nbsp;<strong>$FactoryId = $_REQUEST['FactoryId'];</strong><br />
  <br />
        <span class="codeComment">&nbsp;&nbsp;# Create object of FusionCharts PHP class </span><br />
        &nbsp;&nbsp;$FC = new FusionCharts(&quot;Column2D&quot;,&quot;600&quot;,&quot;300&quot;); </p>
      <p> <span class="codeComment">&nbsp;&nbsp;# Set Relative Path of chart swf file.</span><br />
        &nbsp;&nbsp;$FC-&gt;setSwfPath(&quot;../../FusionCharts/&quot;);<br />
  <br />
        <span class="codeComment">&nbsp;&nbsp;// Store Chart attributes in a variable</span><br />
        &nbsp;&nbsp;$strParam=&quot;caption=Factory &quot; . $FactoryId . &quot; Output;subcaption=(In Units);xAxisName=Date; formatNumberScale=0;decimalPrecision=0&quot;;</p>
      <p> <span class="codeComment">&nbsp;&nbsp;#  Set chart attributes</span><br />
        &nbsp;&nbsp;$FC-&gt;setChartParams($strParam);<br />
  <br />
        <span class="codeComment">&nbsp;&nbsp;// Connect to the DataBase</span><br />
        &nbsp;&nbsp;$link = connectToDB();</p>
      <p> <span class="codeComment">&nbsp;&nbsp;//Now, we get the data for that factory</span><br />
        &nbsp;&nbsp;$strQuery = &quot;select Quantity, DATE_FORMAT(DatePro,'%c/%e') as DDate from Factory_Output where FactoryId=&quot; . $FactoryId;<br />
        <br />
        &nbsp;&nbsp;$result = mysql_query($strQuery) or die(mysql_error());<br />
  <br />
        <span class="codeComment">&nbsp;&nbsp;//Pass the SQL query result to the FusionCharts PHP Class' function <br />
        &nbsp;&nbsp;//that will extract data from databasa and add to the chart.</span><br />
        &nbsp;&nbsp;if ($result) <br />
        &nbsp;&nbsp;{<br />
        &nbsp;&nbsp;&nbsp;&nbsp;$FC-&gt;addDataFromDatabase($result, &quot;Quantity&quot;, &quot;DDate&quot;);<br />
        &nbsp;&nbsp;}<br />
        <br />
        &nbsp;&nbsp;mysql_close($link);</p>
      <p> <span class="codeComment">&nbsp;&nbsp;//Create the chart  - Column2D using FusionCharts PHP Class</span><br />
        &nbsp;&nbsp;$FC-&gt;renderChart();<br />
        ?&gt;<br />
        <br />
  &lt;/CENTER&gt;<br />
  &lt;/BODY&gt;<br />
  &lt;/HTML&gt;</p></td>
  </tr>
  <tr>
    <td valign="top" class="text">Let's analyze the steps involved in the above code: </td>
  </tr>
  <tr>
    <td valign="top" class="text" style="line-height:20px;">
      <ol>
        <li>Including  <span class="codeInline">FusionCharts.js</span> JavaScript class, <span class="codeInline">FusionCharts.php </span> and <span class="codeInline">DBConn.php</span> to enable easy embedding of FusionCharts.</li>
        <li>Requesting the factory id for which we've to show detailed data. This data was sent to us as query string, as a part of pie chart link. FactoryId is stored in a variable <span class="codeInline">$FactoryId</span> </li>
        <li>Creating FusionCharts PHP class object for Column 2D chart</li>
        <li>Setting relative path of chart SWF file</li>
        <li>Storing chart attributes in a variable <span class="codeInline">$strParam</span></li>
        <li>Setting chart attributes through <span class="codeInline">setChartParams()</span> function</li>
        <li>Connecting to MySQL database through <span class="codeInline">connectToDB()</span> function</li>
        <li>Fetching required data from database and storing in a variable <span class="codeInline">$result</span></li>
        <li>Passing <span class="codeInline">$result</span> to <span class="codeInline">addDataFromDatabase()</span> function that adds chart data from database</li>
        <li>Closing database connection</li>
        <li>Rendering chart    </li>
      </ol>    </td>
  </tr>
  <tr>
    <td valign="top" class="highlightBlock">Please go through <a href="PHPClassAPI/Functions.html">FusionCharts PHP Class API Reference</a> section to know more about the functions used in the above code. </td>
	</tr>
  <tr>
    <td valign="top" class="text">&nbsp;</td>
  </tr>
  <tr>
    <td valign="top" class="text">When you now run the app, you'll see the detailed page as under: </td>
  </tr>
  <tr>
    <td valign="top" class="text"><img src="Images/Code_Drill.jpg"  class="imageBorder" />&nbsp;</td>
  </tr>
</table>
</body>
</html>
